<?php
namespace app\home\controller;
use think\Controller;
use think\Cookie;
use think\Db;

/***
 *表单导出
 */
class Count extends Controller
{
    /***表单导出**/
    function export_count($Id){
        /**获取数据库**/
        $count_data=Db::name('count')->where('id',$Id)->find();
        $count_table_data=Db::name('count_table')->where('father_table_id',$Id)->order('class_date')->select();

        /**Excel操作**/
        $col=[
            'A','B','C','D','E','F','G',
            'H','I','J','K','L','M','N',
            '0','P','Q','R','S','T','U',
            'V','W','X','Y','Z'
        ];
        $table_title=[
            '序号','实验名称','学时数',
            '实验类别','要求','实验专业',
            '班级','实验者类别','人数',
            '相同实验组数','每组人数','开出日期',
            '备注'
        ];
        $excel_table=new \PHPExcel();
        //设置sheet
        $excel_table->setActiveSheetIndex(0);
        //合并单元格
        $excel_table->getActiveSheet()->mergeCells('C1:K1');
        $excel_table->getActiveSheet()->mergeCells('F2:H2');
        $excel_table->getActiveSheet()->mergeCells('B3:L3');
        //设置表头
        $excel_table->getActiveSheet()->getStyle('C1')
            ->getFont()
            ->setSize(14)
            ->setName('黑体');
        $excel_table->getActiveSheet()->getStyle('C1')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()->setCellValue('C1','湖北工程学院计算机实验教学示范中心实验开出情况统计表');
        //统计表时间
        $excel_table->getActiveSheet()->getStyle('F2')
            ->getFont()
            ->setSize(9)
            ->setName('Times New Roman');
        $excel_table->getActiveSheet()->getStyle('F2')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()->setCellValue('F2',$count_data['class_date']);
        //统计表信息
        $excel_table->getActiveSheet()->getStyle('B3')->getFont()->setSize(9)->setName('宋体');
        $excel_table->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()->setCellValue('B3',$count_data['college'].'       实验室：'.$count_data['laboratory'].'      实验课程：'.$count_data['class'].'       　实验教师：'.$count_data['teacher'].'   　　实验员：'.$count_data['experimenter']);
        //统计表内容中的表头
        //设置行高
        $excel_table->getActiveSheet()->getRowDimension(4)->setRowHeight(22.9);
        $excel_table->getActiveSheet()->getStyle('A4:M4')
            ->getFont()
            ->setBold(true)
            ->setSize(9)
            ->setName('宋体');
        $excel_table->getActiveSheet()->getStyle('A4:M4')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        for($i=0;$i<=12;$i++)
        {
            $excel_table->getActiveSheet()->setCellValue($col[$i].'4',$table_title[$i]);
        }
        $num=0;
        foreach ($count_table_data as $table_datum)
        {
            $num++;
            //设置行高
            $excel_table->getActiveSheet()->getRowDimension($num+4)->setRowHeight(-1);
            $excel_table->getActiveSheet()->getStyle('A'.(int)($num+4).':M'.(int)($num+4))
                ->getFont()
                ->setSize(9)
                ->setName('宋体');
            $excel_table->getActiveSheet()->getStyle('A'.(int)($num+4).':M'.(int)($num+4))
                ->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
                ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
                ->setWrapText(true);
            $excel_table->getActiveSheet()->setCellValue('A'.(int)($num+4),$num);
            $excel_table->getActiveSheet()->setCellValue('B'.(int)($num+4),$table_datum['experiment_name']);
            $excel_table->getActiveSheet()->setCellValue('C'.(int)($num+4),$table_datum['hours']);
            $excel_table->getActiveSheet()->setCellValue('D'.(int)($num+4),$table_datum['experiment_type']);
            $excel_table->getActiveSheet()->setCellValue('E'.(int)($num+4),$table_datum['requirement']);
            $excel_table->getActiveSheet()->setCellValue('F'.(int)($num+4),$table_datum['experiment_major']);
            $excel_table->getActiveSheet()->setCellValue('G'.(int)($num+4),$table_datum['class']);
            $excel_table->getActiveSheet()->setCellValue('H'.(int)($num+4),$table_datum['experimenter_type']);
            $excel_table->getActiveSheet()->setCellValue('I'.(int)($num+4),$table_datum['total_number']);
            $excel_table->getActiveSheet()->setCellValue('J'.(int)($num+4),$table_datum['similar_num']);
            $excel_table->getActiveSheet()->setCellValue('K'.(int)($num+4),$table_datum['each_num']);
            $excel_table->getActiveSheet()->setCellValue('L'.(int)($num+4),$table_datum['class_date'].'('.$table_datum['section'].'节)');
            $excel_table->getActiveSheet()->setCellValue('M'.(int)($num+4),$table_datum['comment']);
        }
        if($num<16)
        {
            for($i=$num+1+4;$i<=16+4;$i++)
            {

                //设置行高
                $excel_table->getActiveSheet()->getRowDimension($i)->setRowHeight(-1);
                $excel_table->getActiveSheet()->getStyle('A'.($i).':M'.($i))
                    ->getFont()
                    ->setSize(9)
                    ->setName('宋体');
                $excel_table->getActiveSheet()->getStyle('A'.($i).':M'.($i))
                    ->getAlignment()
                    ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
                    ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
                    ->setWrapText(true);
                $excel_table->getActiveSheet()->setCellValue('A'.($i),$i-4);
                for($j=1;$j<=12;$j++)
                    $excel_table->getActiveSheet()->setCellValue($col[$j].($i),"");
            }
            $num=16;
        }
        //边框线风格
        //方法有bug
        $style= array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THICK
                )
            )
        );
        $excel_table->getActiveSheet()->getStyle('A4:M'.($num+4))
            ->applyFromArray($style);


        //设置表尾注
        //合并单元格
        $excel_table->getActiveSheet()->mergeCells('A'.($num+5).':M'.($num+5));
        $excel_table->getActiveSheet()->mergeCells('B'.($num+6).':D'.($num+6));
        $excel_table->getActiveSheet()->mergeCells('E'.($num+6).':H'.($num+6));
        $excel_table->getActiveSheet()->mergeCells('I'.($num+6).':L'.($num+6));
        $excel_table->getActiveSheet()->mergeCells('B'.($num+8).':D'.($num+8));
        $excel_table->getActiveSheet()->mergeCells('E'.($num+8).':H'.($num+8));
        $excel_table->getActiveSheet()->mergeCells('I'.($num+8).':L'.($num+8));
        //设置行高（自适应）
        $excel_table->getActiveSheet()->getRowDimension($num+5)->setRowHeight(26.8);
        $excel_table->getActiveSheet()->getRowDimension($num+6)->setRowHeight(-1);
        $excel_table->getActiveSheet()->getRowDimension($num+8)->setRowHeight(-1);
        $excel_table->getActiveSheet()->getStyle('A'.($num+5).':M'.($num+8))
            ->getFont()
            ->setSize(9)
            ->setName('宋体');
        $excel_table->getActiveSheet()->getStyle('A'.($num+5).':M'.($num+8))
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);
        $excel_table->getActiveSheet()
            ->setCellValue(
                'A'.($num+5),
                "说明：“实验类别”应根据教学计划要求填写：“基础课”、 “专业基础课” 、“专业课” ； “要求” 应填写 ：“必做” 、 “选做” 、“其他”；“实验者类别”填写：“本科生”、 “专科生”、“其他”。"
            )
            ->setCellValue('B'.($num+6),'院（系）领导签字：')
            ->setCellValue('E'.($num+6),'实验室主任签字：')
            ->setCellValue('I'.($num+6),'填表人签字：')
            ->setCellValue('B'.($num+8),'年　　月　　日')
            ->setCellValue('E'.($num+8),'年　　月　　日')
            ->setCellValue('I'.($num+8),'年　　月　　日')
        ;
        header('pragma:public');
        header('Content-Disposition:attachment;filename=统计表.xlsx');
        header('Cache-Control: max-age=0');

        $excel_writer=\PHPExcel_IOFactory::createWriter($excel_table,'Excel2007');
        $excel_writer->save('php://output');
    }
    function section_process($sections){
        if(count($sections)==2){
            if($sections[1]-$sections[0]==1){
                return (string)$sections[0]."-".(string)$sections[1];
            }else{
                return (string)$sections[0].",".(string)$sections[1];
            }
        }else{
            $result="";
            $nums=count($sections);
            foreach ($sections  as $key=>$section){
                if($key>0){
                    if($sections[$key]-$sections[$key-1]==1){
                        if($key+2<=$nums){
                            if($sections[$key+1]-$sections[$key]==1) continue;
                        }
                        $result=(string)$result."-".(string)$section;
                    }else{
                        $result=(string)$result.",".(string)$section;
                    }
                }else{
                    $result=(string)$section;
                }
            }
            return $result;
        }
    }
    function get_person_info(){
        $cookie=new Cookie();
        $safecode=$cookie->get('safecode');
        $data=Db::name('user')->where('safecode',$safecode)->find();
        return $data;
    }


    /**统计表的实验课程**/
    public function count_table(){
        $this->get_person_info();
        $id=input('get.Id');
        $this->assign('Id',$id);
        return $this->fetch('count/count_table',[],['__PUBLIC__'=>'/public/static']);
    }

    public function count_table_json()  //获取统计表内容接口
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $Id=input('get.Id');
        $data=DB::name('count_table')->where(['father_table_id'=>$Id])->select();
        $father=$Id;
        $father_data=Db::name('count')->where('id',$father)->find();
        $father_uid=$father_data['uid'];
        if($uid==$father_uid){
            return json($data);
        }

    }
    public function insert_count_table()    //新增页面
    {
        $this->assign('Id',input('get.Id'));
        return $this->fetch('count/insert_count_table',[],['__PUBLIC__'=>'/public/static']);
    }
    public function into_count_table()  //增加操作
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=input('post.');
        $father_id=$data['father_table_id'];
        $father_data=Db::name('count')->where('id',$father_id)->find();
        if($father_data['uid']!=$person_uid){
            $this->error('无权操作');
        }else{
            $data['section']=$this->section_process($data['section']);
            $res=Db::name('count_table')->insert($data);
            if($res){
                $this->success("添加成功");
            }else{
                $this->error("添加失败");
            }
        }

    }
    public function edit_count_table()  //编辑统计表
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=input('post.');
        $field=$data['field'];
        $Id=$data['Id'];
        $father_data=Db::name('count')->where('id',$data['father_table_id'])->find();
        $data=$data[$field];
        if($father_data['uid']!=$person_uid){
            return "error";
        }else{
            $sql=Db::name('count_table')->where('Id',$Id)->update([$field=>$data]);
            if($sql){
                return "success";
            }else{
                return "error";
            }
        }
    }
    public function delete_count_table()    //删除统计表中的实验课程
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=file_get_contents('php://input');
        $data=json_decode($data,true);
        $delete_list=array();
        foreach ($data as $datum){
            $one=Db::name('count_table')->where('Id',$datum['Id'])->find();
            $father_id=$one['father_table_id'];
            $father_data=Db::name('count')->where('id',$father_id)->find();
            if(!empty($father_data)&&$father_data['uid']==$person_uid){
                $delete_list[]=$datum["Id"];
            }

        }
        $sql=Db::name('count_table')->delete($delete_list);
        if($sql){
            return json([
                'msg'=>'success'
            ]);
        }else{
            return json([
                'msg'=>'error'
            ]);
        }
    }


    //统计表
    public function count() //统计表数据
    {
        return $this->fetch('count/count',[],['__PUBLIC__'=>'/public/static']);
    }
    public function count_json()    //获取统计表数据
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=Db('count')->where('uid',$uid)->select();
        return json($data);
    }
    public function insert_count(){
        return $this->fetch('count/insert_count',[],['__PUBLIC__'=>'/public/static']);
    }
    public function into_count()  //增加操作
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=input('post.');
        $data['uid']=$uid;
        if(strtotime($data['class_date'][0])<strtotime($data['class_date'][1]))
            $data['class_date']=$data['class_date'][0].'至'.$data['class_date'][1];
        else
            $data['class_date']=$data['class_date'][1].'至'.$data['class_date'][0];
        $res=Db::name('count')->insert($data);

        if($res){
            $this->success("添加成功");
        }else{
            $this->error("添加失败");
        }
    }
    public function edit_count()    //编辑统计表信息
    {
        $data=input('post.');
        $field=$data['field'];  //要修改的字段
        //获取当前用户信息
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $Id=$data['id'];
        $count_data=Db::name('count')->where('id',$Id)->find();
        if($count_data['uid']==$uid){
            $sql=Db::name('count')->where('id',$Id)->update([$field=>$data[$field]]);
            if($sql)
            {
                return "success";
            }else{
                return "error";
            }
        }else{
            return "error";
        }
    }
    public function delete_count(){
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=file_get_contents('php://input');
        $data=json_decode($data,true);
        $delete_list=array();
        foreach ($data as $datum){
            $one=Db::name('count')->where('id',$datum['id'])->find();
            if($one['uid']==$uid)
                $delete_list[]=$datum['id'];
        }
        $sql=Db::name('count')->delete($delete_list);
        if($sql){
            return json([
                'msg'=>'success'
            ]);
        }else{
            return json([
                'msg'=>'error'
            ]);
        }
    }




    /**********导出******/
    public function export()    //导出统计表
    {
        $Id=input('get.Id');
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $count_data=Db::name('count')->where('id',$Id)->find();
        if($uid!=$count_data['uid'])
        {
            $this->error('越权操作');
            return;
        }
        $this->export_count($Id);
    }
}
